﻿Imports System.Data.SqlClient

Module modData
    Public ConnectionString As String = "Persist Security Info=True;User ID=sa; password=egen;Initial Catalog=batch;Data Source=ASUS-PC"

    Public Function GetScalar(ByVal sql As String) As Object
        Dim dt As DataTable = GetTable(sql)
        If dt.Rows.Count = 0 Then
            Return Nothing
        Else
            Return dt.Rows(0)(0)
        End If
    End Function


    Public Function GetScalar(ByVal table As String, ByVal field As String, ByVal criteria As String) As Object
        Dim x As String = "SELECT " + field + " FROM " + table
        If criteria <> "" Then
            x += " WHERE " + criteria
        End If
        Return GetScalar(x)
    End Function


    Public Function GetTable(ByVal sql As String) As DataTable
        Dim da As New SqlDataAdapter(sql, ConnectionString)
        Dim dt As New DataTable

        Try
            da.Fill(dt)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Return dt
    End Function

    Public Function GetTable(ByVal table As String, ByVal fields As String, Optional ByVal criteria As String = "") As DataTable
        Dim s As String = "SELECT " + fields + " FROM " + table
        If criteria <> "" Then
            s += " WHERE " + criteria
        End If
        Return GetTable(s)
    End Function

    Public Function ExecuteSQL(ByVal sql As String) As Integer
        Dim conn As New SqlClient.SqlConnection(ConnectionString)
        Dim com As New SqlClient.SqlCommand(sql, conn)
        Dim x As Integer
        Try
            conn.Open()
            x = com.ExecuteScalar()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try
        Return x
    End Function

    Public Function ExecuteSQLAndReturnKey(ByVal sql As String) As Integer
        Dim conn As New SqlClient.SqlConnection(ConnectionString)
        Dim com As New SqlClient.SqlCommand(sql, conn)
        Dim x As Integer
        Try
            conn.Open()
            x = com.ExecuteScalar()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try
        Return x
    End Function

    Public Function GetMax(table As String, field As String) As Integer
        Return GetScalar(table, String.Format("max({0})", field), "")
    End Function
End Module
